package com.filldream.fastboot.common.util;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import com.filldream.fastboot.common.exception.GlobalException;
import com.filldream.fastboot.common.entity.ErrorCode;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;

/**
 * @author RickSun && iFillDream
 * @version 1.0
 * @data 2020-06-05 9:47
 * @description Excel工具类
 */
public class ExcelUtil {

    //--------------导入Excel-----------

    /**
     * 导入为Bena列表
     * @param reader   ExcelReader
     * @param clazz Bean类型
     * 1、只导入表头和字段名一致的数据
     * 2、若Excel没有表头则无法导入！
     * @return
     */
    public static <T>List<T> readToBean(ExcelReader reader,Class<T> clazz){
        return readToBean(reader,Collections.emptyMap(),clazz);
    }

    /**
     * 导入为Bean列表
     * @param reader    ExcelReader
     * @param headMap   表头map,k-为excel表头名 v-为bean属性名 一一对应
     * @param clazz Bean类型
     * 1、只导入表头和字段名一致的数据
     * 2、若Excel没有表头则无法导入！
     * @return
     */
    public static <T>List<T> readToBean(ExcelReader reader,Map<String,String> headMap,Class<T> clazz){
        if( headMap != null && !headMap.isEmpty() ){
            reader.setHeaderAlias(headMap);
        }
        reader.setIgnoreEmptyRow(true);
        List<T> list = reader.readAll(clazz);
        return list;
    }

    /**
     * 导入为Bean列表，可导入无表头Excel
     * @param reader    ExcelReader
     * @param beanFields 字段名,和excel保持一致的顺序
     * @param clazz Bean类型
     * @param <T>
     * @return
     */
    public static <T>List<T> readToBean(ExcelReader reader,List<String> beanFields,Class<T> clazz){
        List<T> resultList = new ArrayList<>();
        reader.setIgnoreEmptyRow(true);
        int rowCount = reader.getRowCount();
        int columnCount = reader.getColumnCount();
        try {
            for (int i = 0; i < rowCount; i++) {
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < columnCount; j++) {
                    map.put(beanFields.get(j), reader.readCellValue(j, i));
                }
                resultList.add((T) JsonUtil.fromJson(JsonUtil.toJson(map), clazz));
            }
        }catch (Exception e){
            throw new GlobalException(ErrorCode.BASE_ERR.reMsg("读取Excel错误"),e,true);
        }
        return resultList;
    }


    //--------------导出Excel-----------

    /**
     * 导出默认格式Excel
     * @param headMap   表头Map,必须为有序MAP，可以为空 k-bean属性 v-别名
     * @param rows  数据列表
     * @param response  httpServletResponse
     */
    public static  <T>void exportNormal(Map<String,String> headMap, List<T> rows, HttpServletResponse response) {
        export(headMap,rows,null,null,response);
    }

    /**
     * 导出Excel
     * @param headMap   表头Map,必须为有序MAP，可以为空，若表头数量小于实际列数则默认为bean的字段名，这里采用强制一致，可以为空 k-bean属性 v-别名
     * @param rows  数据列表
     * @param sheetName sheet名称,默认为sheet1
     * @param fileName  文件名称，默认为uuid
     * @param response  httpServletResponse
     */
    public static  <T>void export(Map<String,String> headMap, List<T> rows,String sheetName,String fileName, HttpServletResponse response) {
        // 通过工具类创建writer，默认创建xls格式,传入true表示为xlxs
        ExcelWriter writer = new ExcelWriter(false, sheetName);

        //是否有自定的表头别名
        if (headMap != null && !headMap.isEmpty()) {
            writer.setHeaderAlias(headMap);
        }

        //写入表格
        writer.write(rows, true);

        //判断表头数量是否一致
        int columnCount = writer.getColumnCount();
        if(!headMap.isEmpty() && columnCount != headMap.size()){
            throw new GlobalException(ErrorCode.DOWNLOAD_ERR.reMsg(":表头名称数量不一致"),true);
        }

        //居中、自适应列宽
        writer.autoSizeColumnAll();
        setSizeColumn(writer.getSheet(), columnCount);


        //设置文件名称,默认uuid，中文需转码
        fileName = FileIOUtil.getFileChineseName(fileName);

        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
        ServletOutputStream out= null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
            throw new GlobalException(ErrorCode.DOWNLOAD_ERR.reMsg(":Excel"),e,true);
        }

        writer.flush(out, true);

        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }


    /**
     * 自动列宽
     * 超出200按200长度计算
     * @param sheet
     * @param size
     */
    private static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                Cell currentCell = currentRow.getCell(columnNum);
                String s = currentCell.toString();
                if(StringUtils.isBlank(s)){s="";}
                if (currentRow.getCell(columnNum) != null) {
                    int length = s.getBytes().length;
                    if( length > 200 ){
                        length = 200;
                    }
                    if (columnWidth < length) {
                        columnWidth = length;
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

}
